from sqlalchemy import create_engine

# 1. 连接数据库并查询

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/test", max_overflow=5)
print(engine)
result = engine.execute("select * from sud")
print(result.fetchall)


#############################################################
# 2. 创建表：
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey

metadata = MetaData() # 相当于实例一个父类
user = Table('user', metadata, # 相当于让Table继承metadata类
             Column('id', Integer, primary_key=True),
             Column('name', String(20)),
            )
color = Table('color', metadata, # 表名color
              Column('id', Integer, primary_key=True),
              Column('name', String(20)),
            )
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/test", max_overflow=5)
metadata.create_all(engine)

###########################################################
# 3. CRUD
from sqlalchemy import create_engine, Table, Column, Integer, String, ForeignKey, select
metadata = MetaData()
# 3.1 原生sql
conn = engine.connect() # 创建游标，当前实例所处状态
conn.execute(user.insert(), {'id':1, 'name':'zcl'}) # 创建sql语句

sql = user.insert().values(name='wu') # 插入
conn.execute(sql)
conn.close()

sql = user.delete().where(user.c.id > 1) # 删除id大于1的行
conn.execute(sql)
conn.close()

sql = user.update().where(user.c.name == 'wuu').values(name='ed') # 更新
conn.execute(sql)
conn.close()

sql = select([user.c.id,]) # 查询，不能user.select 会报错
sql = select([user.c.name, color.c.name]).where(user.c.id == color.c.id)

result = conn.execute(sql)
print(result.fetchall())
conn.close()

# 3.2 通过sqlalchemy CRUD
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base() # 生成一个SqlORM基类(已封装metadata)
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/test", max_overflow=5, echo=True) # echo=True可以查看创建表的过程
class Host(Base):
    __tablename__ = 'hosts' # 表名为host
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    port = Column(Integer, default=22)

Base.metadata.create_all(engine) # 创建所有表结果
SessionCls = sessionmaker(bing=engine)
session = SessionCls() # 连接的实例

# 准备插入数据
h1 = Host(hostname='localhost', ip_addr='127.0.0.1') # 实例化(未创建)
h2 = Host(hostname='ubuntu', ip_addr='192.168.2.243', port=20000)

session.add(h1) # 也可以用下面的批量处理
session.add_all([h1, h2])
h2.hostname = 'ubuntu_test' # 只要没提交，此时修改也没问题
# 查询数据，返回一个对象
obj = session.query(Host).filter(Host.hostname == 'localhost').first()
print("--->", obj)
# 如果用.all() 会报错
obj.hostname = "localhost_1" # 将主机名修改为localhost_1
session.delete(obj) # 删除行
session.commit() # 提交


###########################################################
# 4. 外键关联
from sqlalchemy.orm import sessionmaker, relationship
class Host(Base):
    __tablename__ = 'hosts' # 表名为host
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    port = Column(Integer, default=22)
    # 外键关联， 主机与组名关联
    group_id = Column(Integer, ForeignKey('group.id'))
class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(46), unique=True, nullable=False)
Base.metadata.create_all(engine)

g1 = Group(name = "g1")
g2 = Group(name = "g2")
g3 = Group(name = "g3")
g4 = Group(name = "g4")
session.add_all([g1, g2, g3, g4])

h1 = Host(hostname = 'localhost', ip_addr='127.0.0.1', group_id=g1.id)
session.add(h1)
session.commit() # 提交

g4 = session.query(Group).filter(Group.name=='g4').first() # 找到g4组的对象
h = session.query(Host).filter(Host.hostname=='localhost').update({'group_id':g4.id}) # 更新(修改)
session.commit() # 提交


###########################################################
# 5. 合并查询
obj = session.query(Host).join(Host.group).add() # 相当于inner join
print("---> obj:", obj)

##########################################################
# 6. 分类聚合
from sqlalchemy import func

obj1 = session.query(Host).join(Host.group).group_by(Group.name).all()
obj2 = session.query(Host, func.count(Group.name)).join(Host.group).group_by(Group.name).all()

##############################################################
# 7. 多对多关联
# 7.1 建立中间表，关联其他两个表，直接创建表并返回表的实例， Host2Group主动关联Host和Group
Host2Group = Table('host_to_group', Base.metadata,
                   Column('host_id', ForeignKey('host.id'), primary_key = True),
                   Column('group_id', ForeignKey('group.id'), primary_key = True),
                   # 一个表为什么能创建两个主键(其实两个列同时作为主键，非空且唯一)
                   # PRIMARY KEY (host_id, group_id)
                   )
# 7.2 在Host表(或Group表)指定中间表的实例，加上backref就不用再Group表中指定

